"""
Dump a SQL dataset into a sqlite db + ini file.

"""

import urlparse
import sqlite3
from cStringIO import StringIO

from configobj import ConfigObj
from httplib2 import Http
import numpy

from pydap.client import open_url


sqlite3.register_adapter(numpy.float32, float)
sqlite3.register_adapter(numpy.float32, float)
sqlite3.register_adapter(numpy.int32, int)


if __name__ == '__main__':
    import sys
    import os

    url = sys.argv[1]
    basename = urlparse.urlsplit(url).path.rsplit('/', 1)[1].rsplit('.', 1)[0]

    # download INI file
    h = Http()
    resp, content = h.request(url)
    buf = StringIO()
    buf.write(content)
    buf.seek(0)
    config = ConfigObj(buf, unrepr=True)
    config['database']['dsn'] = 'sqlite://%s/%s.db' % (os.getcwd(), basename)
    config['database']['table'] = 'data'
    config.filename = '%s.sql' % basename
    config.write()

    # create table
    connection = sqlite3.connect('%s.db' % basename, detect_types=sqlite3.PARSE_DECLTYPES)
    connection.executescript("CREATE TABLE data (__updated__ timestamp);")
    cols = [section for section in config.values() if 'col' in section]
    for section in cols:
        column_name = section['col']
        if 'type' in section and section['type'].lower() in ['string', 'url']:
            type_name = 'TEXT'
        else:
            type_name = 'NUMBER'
        connection.executescript("ALTER TABLE data ADD COLUMN %s %s;" % (column_name, type_name))

    # load dataset and save to sqlite
    dataset = open_url(url)

    # add data
    try:
        sequence = config['sequence']['name']
    except KeyError:
        sequence = 'sequence'
    for value in dataset[sequence]:
        cols = ', '.join([ config[k]['col'] for k in value.keys() ])
        markers = ', '.join([ '?' for var in value.keys() ])
        query = "INSERT INTO data (%s) VALUES (%s)" % (cols, markers)
        connection.execute(query, value.data)

    connection.commit()
